<?php

namespace App\Http\Controllers\Admin;

use App\Http\Controllers\Admin\Traits\AdminTrait;
use App\Http\Controllers\Controller;
use Illuminate\Http\Request;
use DB;

class SalesAnalysisController extends Controller
{
    use AdminTrait;

    protected $connection;

    public function __construct()
    {
        $this->connection = DB::connection('slave_mysql');
    }

    public function index(Request $request)
    {
        $page = $request->query('page') ?? 1;
        $per_page = $request->query('per_page') ?? 20;
        $type = $request->query('type');
        $send_time = $request->query('send_time') ?? date("Y-m-d");
        $order_ids = $this->connection->table('order')->whereIn('order_status',[0,1,2,3])->where('send_time',strtotime($send_time))->pluck('id');
        if ($order_ids->isEmpty()) {
            return $this->success(['list'=>[],'count'=>0]);
        }
        $goods_ids = $request->query('goods_ids') ?? '';
        $select = $this->connection->table('order_list')
            ->selectRaw('name,spu_name,unit,company_id,(SELECT standard_spu_id FROM '.config('database.connections.slave_mysql.prefix').'goods_spu WHERE id=goods_spu_id LIMIT 1) as standard_spu_id')
            ->selectRaw('(SELECT standard_goods_id FROM '.config('database.connections.slave_mysql.prefix').'goods WHERE id=goods_id LIMIT 1) as standard_goods_id')
            ->selectRaw('(SELECT base_address FROM '.config('database.connections.slave_mysql.prefix').'company WHERE id=company_id) as region_id')
            ->selectRaw("CONCAT(company_id,'-',(SELECT standard_spu_id FROM ".config('database.connections.slave_mysql.prefix')."goods_spu WHERE id=goods_spu_id LIMIT 1)) as company_spu")
            ->selectRaw("ROUND(AVG(sort_num),2) as avg_num")
            ->selectRaw("ROUND(AVG(sort_price),2) as avg_price")
            ->where('standard_spu_code','!=','')->whereIn('oid',$order_ids);
        if (!empty($goods_ids)) {
            $standard_code = $this->connection->table('goods_standard')->whereRaw('id in ('.$goods_ids.')')->pluck('standard_code');
            $select = $select->whereIn('standard_code',$standard_code);
        }
        if ($type == 1) {
            $region_ids = $request->query('region_ids');
            if (empty($region_ids)) {
                return $this->success(['list'=>[],'count'=>0]);
            }
            $region_ids = explode(',',$region_ids);
            $company_ids = $this->connection->table('company')->whereIn('base_address',$region_ids)->pluck('id');
            $regions = $this->connection->table('admin_region')->select('id','name')->whereIn('id',$region_ids)->get()->keyBy('id');
            if ($company_ids->isNotEmpty()) {
                $list = $select->whereIn('company_id',$company_ids)->groupBy('company_spu')->get();
                if ($list->isNotEmpty()) {
                    $data = $list->groupBy('standard_spu_id')->transform(function ($item) use ($regions) {
                        $prices = $nums =[];
                        $total_num = 0;
                        foreach ($item as $v) {
                            $total_num = $total_num + $v->avg_num;
                            if (isset($prices[$v->region_id])) {
                                $prices[$v->region_id][] = $v->avg_price;
                            } else {
                                $region = $regions[$v->region_id];
                                $prices[$v->region_id] = [
                                    'region_name' => $region->name,
                                    'num_arr' => [
                                        $v->avg_num
                                    ],
                                    'price_arr' => [
                                        $v->avg_price
                                    ],
                                    'num' => 0,
                                    'price' => 0,
                                ];
                            }
                        }
                        foreach ($prices as $k => $v) {
                            $price = $num = 0;
                            $count = count($v['price_arr']);
                            foreach ($v['num_arr'] as $vv) {
                                $num += $vv;
                            }
                            foreach ($v['price_arr'] as $vv) {
                                $price += $vv;
                            }
                            $prices[$k]['num'] = $num;
                            $prices[$k]['price'] = round(($price / $count), 2);
                            unset($prices[$k]['num_arr']);
                            unset($prices[$k]['price_arr']);
                        }
                        $prices = array_values($prices);
                        $data = [
                            'name' => $item->first()->name,
                            'spu_name' => $item->first()->spu_name,
                            'unit' => $item->first()->unit,
                            'nums' => sprintf("%.2f",round($total_num,2)),
                            'prices' => $prices
                        ];
                        return $data;
                    });
                    $count = count($data);
                    $data = $data->chunk($per_page);
                    $list = array_values($data[$page - 1]->toarray());
                    return $this->success(['list' => $list, 'count' => $count]);
                }
            }
            return $this->success(['list'=>[],'count'=>0]);
        } elseif ($type == 2) {
            $company_ids = $request->query('company_ids');
            if (empty($company_ids)) {
                return $this->success(['list'=>[],'count'=>0]);
            }
            $company_ids = explode(',',$company_ids);
            $companies = $this->connection->table('company')->select('id','company_name')->get()->keyBy('id');
            $list = $select->whereIn('company_id',$company_ids)->groupBy('company_spu')->get();
            if ($list->isEmpty()) {
                return $this->success(['list'=>[],'count'=>0]);
            }
            $data = $list->groupBy('standard_spu_id')->transform(function ($item) use ($companies){
                $prices = [];
                $total_num = 0;
                foreach ($item as $v) {
                    $total_num = $total_num + $v->avg_num;
                    $company = $companies[$v->company_id];
                    $prices[] = [
                        'company_name' => $company->company_name,
                        'num' => $v->avg_num,
                        'price' => $v->avg_price
                    ];
                }
                $data = [
                    'name' => $item->first()->name,
                    'spu_name' => $item->first()->spu_name,
                    'unit' => $item->first()->unit,
                    'nums' => sprintf("%.2f",round($total_num,2)),
                    'prices' => $prices
                ];
                return $data;
            });
            $count = count($data);
            $data = $data->chunk($per_page);
            $list = array_values($data[$page-1]->toarray());
            return $this->success(['list'=>$list,'count'=>$count]);
        } else {
            return $this->success(['list'=>[],'count'=>0]);
        }
    }


    public function getGoodsList(Request $request)
    {
        $list = $this->connection->table('goods_category')->where('company_id',0)->orderBy('pid','asc')->orderBy('ordid','asc')->get()->keyBy('id');
        $categoryList = [];
        if (!empty($list)) {
            $categoryList = $this->generateTree(json_encode($list,JSON_UNESCAPED_UNICODE));
        }

        $category_ids = $request->query('category_ids');
        $name = $request->query('name');

        $goods_ids= [];
        if (!empty($name) || !empty($category_ids)) {
            $goods_ids = $this->connection->table('goods_standard')->when(!empty($name),function ($query)use ($name){
                $query->where('name','like',"%$name%");
            })->when(!empty($category_ids),function ($query)use ($category_ids) {
                $query->whereRaw(" category_id in ($category_ids)");
            })->pluck('id');
        }

        $page = $request->query('page') ?? 1;
        $per_page = $request->query('per_page') ?? 20;

        $list = $this->connection->table('goods_standard_spu')
            ->selectRaw("id,standard_goods_id,(SELECT name FROM ".config("database.connections.slave_mysql.prefix")."goods_standard WHERE id=standard_goods_id) as goods_name")
            ->selectRaw("(SELECT pic FROM ".config("database.connections.slave_mysql.prefix")."goods_standard WHERE id=standard_goods_id) as pic")
            ->selectRaw("(SELECT title FROM ".config("database.connections.slave_mysql.prefix")."goods_datum WHERE id=spu_name) as spu_name")
            ->selectRaw("(SELECT title FROM ".config("database.connections.slave_mysql.prefix")."goods_datum WHERE id=unit) as unit")
            ->when(!empty($goods_ids) && $goods_ids->isNotEmpty(),function ($query) use ($goods_ids){
                $query->whereIn('standard_goods_id',$goods_ids);
            })->skip((($page-1)*$per_page))->limit($per_page)->get();
        $count = $this->connection->table('goods_standard_spu')
            ->when(!empty($goods_ids) && $goods_ids->isNotEmpty(),function ($query) use ($goods_ids){
                $query->whereIn('standard_goods_id',$goods_ids);
            })->count();

        return $this->success([
            'categoryList' => $categoryList,
            'list' => $list,
            'count' => $count
        ]);
    }

    public function getRegion(Request $request)
    {
        $base_ids = $this->connection->table('company')->pluck('base_address');
        if ($base_ids->isEmpty()) {
            return $this->success(['data'=>[]]);
        }
        $zbase = $this->connection->table('admin_region')->select('id','name','pid')->whereIn('id',$base_ids)->get();
        $base_zpids = $zbase->pluck('pid');
        $zbase = $zbase->groupBy('pid');
        $pbase = $this->connection->table('admin_region')->select('id','name','pid')->whereIn('id',$base_zpids)->get();
        $base_pids = $pbase->pluck('pid');
        $pbase = $pbase->groupBy('pid');
        $base = $this->connection->table('admin_region')->select('id','name','pid')->whereIn('id',$base_pids)->get();
        $data = [];
        foreach ($base as $k => $v) {
            $current = $pbase[$v->id];
            $next = [];
            foreach ($current as $key => $val) {
                $current_p = $zbase[$val->id];
                $z_next = [];
                foreach ($current_p as $kk => $vv) {
                    $z_next[$kk] = [
                        'id' => $vv->id,
                        'name' => $vv->name,
                        'selfNext' => []
                    ];
                }
                $next[$key] = [
                    'id' => $val->id,
                    'name' => $val->name,
                    'selfNext' => $z_next
                ];
            }
            $data[$k] = [
                'id' => $v->id,
                'name' => $v->name,
                'selfNext' => $next
            ];
        }
        return $this->success(['data'=>$data]);
    }

    public function getCustomers(Request $request)
    {
        $page = $request->query('page') ?? 1;
        $per_page = $request->query('per_page') ?? 20;
        $company_name = $request->query('company_name') ?? '';
        $type = $request->query('type') ?? 0;
        $status = $request->query('status') ?? 0;
        $province_id = $request->query('province_id') ?? 0;
        $city_id = $request->query('city_id') ?? 0;
        $county_id = $request->query('county_id') ?? 0;

        $where = [];
        if ($county_id) {
            $where[] = ['base_address','=',$county_id];
        } elseif ($city_id) {
            $ids_arr=$this->getCountyIdsByPid($city_id,2);
            if(!empty($ids_arr)) {
                $ids_arr=implode(',',$ids_arr);
                $where[]=[DB::raw('base_address in ('.$ids_arr.')'),1];
            }
        } elseif ($province_id) {
            $ids_arr=$this->getCountyIdsByPid($province_id);
            if(!empty($ids_arr)) {
                $ids_arr=implode(',',$ids_arr);
                $where[]=[DB::raw('base_address in ('.$ids_arr.')'),1];
            }
        }

        $list = $this->connection->table('company')
            ->selectRaw("id,company_name,name,mobile,status,base_address")
            ->selectRaw("(SELECT mername FROM ".config("database.connections.slave_mysql.prefix")."admin_region WHERE id=base_address) as base_detail")
            ->when(!empty($company_name),function ($query)use ($company_name){
                $query->where('company_name','like',"%$company_name%");
            })->when($type,function ($query)use ($type){
                $query->where('model',$type);
            })->when($status,function ($query)use ($status){
                //$query->where('status',$status-1);
                if ($status == 2) {
                    $query->where('status',0);
                } else {
                    $query->where('status',1);
                }
            })->when(!empty($where),function ($query)use ($where){
                $query->where($where);
            })->skip((($page-1)*$per_page))->limit($per_page)->get();

        $count = $this->connection->table('company')
            ->when(!empty($company_name),function ($query)use ($company_name){
                $query->where('company_name','like',"%$company_name%");
            })->when($type,function ($query)use ($type){
                $query->where('model',$type);
            })->when($status,function ($query)use ($status){
                if ($status == 2) {
                    $query->where('status',0);
                } else {
                    $query->where('status',1);
                }
            })->when(!empty($where),function ($query)use ($where){
                $query->where($where);
            })->count();

        $status_list = [0 => '所有', 1 => '正常', 2 => '关闭'];
        $type_list = [0 => '所有', 1 => '批发', 2 => '城配', 3 => '软件'];

        return $this->success([
            'list' =>$list,
            'count' => $count,
            'status_list' => $status_list,
            'type_list' => $type_list
        ]);
    }

    protected function generateTree($data){
        $items = json_decode($data,true);
        //$items = $data->toArray();
        $tree = array();
        foreach($items as $k => $item){
            $items[$k]['selfNext'] = [];
            if(isset($items[$item['pid']])){
                //$item['selfNext'] = [];
                $items[$item['pid']]['selfNext'][] = &$items[$k];
            }else{
                $tree[] = &$items[$k];
            }
        }
        return $tree;
    }

    protected function getCountyIdsByPid($pid,$level=1)
    {
        $city_ids_arr = [];
        if($level==1)
        {
            $city_ids_arr = $this->connection->table('admin_region')->where('pid',$pid)->select('id')->get()
                ->transform(function ($i,$k)
                {
                    $i=$i->id;
                    return $i;
                })->toArray();
        }
        elseif($level==2)
        {
            $city_ids_arr = [$pid];
        }
        $county_ids_arr = $this->connection->table('admin_region')->whereIn('pid',$city_ids_arr)->select('id')->get()
            ->transform(function ($i,$k)
            {
                $i=$i->id;
                return $i;
            })->toArray();
        return $county_ids_arr;
    }
}
